# -*- coding = utf-8 -*-
#@Time: 2021/7/3 18:14
#@Author: 卜白
#@File: models.py
#@Software: PyCharm

# 数据库命令操作 sql
from flask import current_app
from base_model import Base_Model

class Book(Base_Model):
    # def __init__(self):     # 创建对象同时要执行的代码
    #     # 连接mysql数据库
    #     self.conn = connect(
    #         host=MYSQL_HOST,
    #         port=MYSQL_PORT,
    #         user=MYSQL_USER,
    #         password=MYSQL_PASSWORD,
    #         database=MYSQL_DATABASE,
    #         charset='utf8'
    #     )
    #     self.cursor = self.conn.cursor(DictCursor)  # 使其运行的SQL命令结果返回字典的形式
    #
    # def __del__(self):  # 释放对象的同时要执行的代码
    #     self.cursor.close()     # 关闭游标
    #     self.conn.close()       # 关闭数据库连接对象

    # 获取 以小说类别分组 的前N本小说信息
    def get_book_infos_limit(self,limitnum):
        sql = 'SELECT * FROM book_infos GROUP BY book_cate DESC LIMIT {}'.format(limitnum)
        # sql = 'SELECT * FROM book_infos GROUP BY book_cate HAVING book_id = "1"'
        self.cursor.execute(sql)

        data = []
        for result in self.cursor.fetchall():
            data.append(result)

        return data

    # 获取完结小说以 更新时间最早分组 的前7本小说信息
    def get_complete_book_limit(self):
        sql = "SELECT book_id,book_cate,book_name,book_author,book_last_update_time FROM book_infos WHERE book_status = '完本' AND book_cate != '全本' GROUP BY book_last_update_time DESC LIMIT 7;"
        self.cursor.execute(sql)

        data = []
        for result in self.cursor.fetchall():
            data.append(result)

        return data

    # 获取各类别小说 以更新时间降序排列
    def get_cates_desc_limit(self,book_cate):
        data = [{"result": []}]
        # 获取分类后的第一本小说
        first_sql = "SELECT book_id,book_cate,book_name,image_urls,book_author,book_desc FROM book_infos WHERE book_cate = '{}' ORDER BY book_last_update_time DESC LIMIT 1;".format(book_cate)
        self.cursor.execute(first_sql)

        first_data = {
            "first_data": self.cursor.fetchall()[0]
        }
        data.append(first_data)

        # 获取分类后的 2-12本小说
        list_sql = "SELECT book_id,book_cate,book_name,book_author FROM book_infos WHERE book_cate = '{}' ORDER BY book_last_update_time DESC LIMIT 1,12;".format(book_cate)
        self.cursor.execute(list_sql)

        for result in self.cursor.fetchall():
            data[0]['result'].append(result)

        return data

    # 首页最近更新以及言情小说的 limit30
    def get_update_romance(self):
        data = [
            {
                'update_time': [],  # 最近更新
                'romance_data': []  # 言情
            }
        ]
        # 查询最近更新的前三十条小说信息
        update_sql = 'SELECT book_id,book_cate,book_name,book_author,book_newest_name,book_newest_url FROM book_infos ORDER BY book_last_update_time DESC LIMIT 30;'
        self.cursor.execute(update_sql)
        for result in self.cursor.fetchall():
            data[0]['update_time'].append(result)

        # 查询最近更新的前三十条言情小说信息
        romance_sql = "SELECT book_id,book_cate,book_name,book_author FROM book_infos WHERE book_cate = '言情' ORDER BY book_last_update_time DESC LIMIT 30;"
        self.cursor.execute(romance_sql)
        for result in self.cursor.fetchall():
            data[0]['romance_data'].append(result)

        return data

    # fiction_cates分类小说栏目 前limit6条数据
    def get_fiction_cates_limit_six(self,fiction_cate):
        six_sql = "SELECT book_id,book_cate,book_name,book_author,book_desc,image_urls FROM book_infos WHERE book_cate = '{}' LIMIT 6;".format(fiction_cate)
        self.cursor.execute(six_sql)

        data = []
        for result in self.cursor.fetchall():
            data.append(result)

        return data

    # fiction_cates分类小说栏目 分页数据  pagenum当前页数  pagesize每页条数
    def get_fiction_cate_pagination(self,fiction_cate,pagenum,pagesize):
        data = []
        # 总数据
        total_sql = "SELECT COUNT(*) as total FROM book_infos WHERE book_cate = '{}';" .format(fiction_cate)
        self.cursor.execute(total_sql)
        total = self.cursor.fetchall()

        # 分页查询
        page_sql = "SELECT book_id,book_cate,book_name,book_author,DATE_FORMAT(book_last_update_time, '%m-%d') AS dtime,book_newest_name,book_newest_url FROM book_infos WHERE book_cate = '{a}' ORDER BY book_last_update_time DESC LIMIT {b} OFFSET {c};".format(a=fiction_cate,b=pagesize,c=(pagenum-1) * pagesize)
        self.cursor.execute(page_sql)

        json_data = {
            "data": data,
            "total": total[0]['total'],
            "status": 200,
            "msg": "请求成功"
        }

        for result in self.cursor.fetchall():
            data.append(result)

        return json_data

    # 好看的分类小说 limit  fiction_cate小说分类   pagesize条数————需优化
    def get_good_look_fiction(self, fiction_cate, pagesize):
        good_sql = "SELECT book_id,book_name,book_author FROM book_infos WHERE book_cate = '{a}' LIMIT {b};".format(a=fiction_cate,b=pagesize)
        self.cursor.execute(good_sql)
        data = []
        for result in self.cursor.fetchall():
            data.append(result)

        return data

    # 获取小说详情页
    def get_book_infos_by_book_id(self,book_ids):
        sql = "select book_id,book_cate,book_name,image_urls,book_author,DATE_FORMAT(book_last_update_time, '%Y-%m-%d %H:%i:%S') AS dtime,book_newest_name,book_newest_url,book_desc from book_infos where book_id ='{}'".format(book_ids)
        self.cursor.execute(sql)
        data = []
        for temp in self.cursor.fetchall():
            data.append(temp)
        return data

    # 获取小说详情页 推荐limit 10
    def get_book_info_recom_limit(self,fiction_cate):
        sql = "SELECT book_id,book_name FROM book_infos WHERE book_cate = '{}' LIMIT 8;".format(fiction_cate)
        self.cursor.execute(sql)
        data = []
        for temp in self.cursor.fetchall():
            data.append(temp)
        return data

    # 获取小说详情页最新章节 前12章
    def get_book_detail_newest_twelve(self,book_id):
        twelve_sql = "SELECT id,book_id,sort_id,detail_title FROM book_details WHERE book_id= '{}' ORDER BY sort_id DESC LIMIT 12".format(book_id)
        self.cursor.execute(twelve_sql)
        data = []
        for temp in self.cursor.fetchall():
            data.append(temp)
        return data

    # 获取小说详情页的全部章节
    def get_book_detail_all_chapter(self,book_id):
        all_sql = "SELECT id,book_id,sort_id,detail_title FROM book_details WHERE book_id= '{}' ORDER BY sort_id".format(book_id)
        self.cursor.execute(all_sql)
        data = []
        for temp in self.cursor.fetchall():
            data.append(temp)
        return data

    # 获取小说章节详情信息数据
    def get_fiction_chapter_content(self,sort_id,book_id):
        chapter_sql = "SELECT bd.`id`,bd.`book_id`,bd.`sort_id`,bi.`book_cate`,bi.`book_name`,bd.`detail_title`,bd.`detail_content` FROM book_details bd JOIN book_infos bi ON bd.`book_id` = bi.`book_id` WHERE bd.sort_id='{a}' and bd.book_id= '{b}';".format(
            a=sort_id,b=book_id)
        self.cursor.execute(chapter_sql)
        data = []
        for temp in self.cursor.fetchall():
            data.append(temp)
        return data

    # 查询下一章的
    def get_next_cap_id(self, book_id, sort_id):
        sql = "select sort_id from book_details where (book_id='{a}') and (sort_id>'{b}') order by sort_id limit 1".format(a=book_id, b=sort_id)
        self.cursor.execute(sql)
        # print("下一章：self.cursor.fetchone() = ", self.cursor.fetchone())
        return self.cursor.fetchone()

    # 查询上一章的
    def get_before_cap_id(self, book_id, sort_id):
        sql = "select sort_id from book_details where (book_id='{a}') and (sort_id<'{b}') order by sort_id desc limit 1".format(a=book_id, b=sort_id)
        self.cursor.execute(sql)
        # print("上一章：self.cursor.fetchone() = ", self.cursor.fetchone())
        return self.cursor.fetchone()

    # 搜索接口 or book_author like 'b=%s
    def search_infos_by_key(self, key):
        sql = "select id,book_id,book_cate,book_name,book_author,book_status,book_newest_name,book_newest_url from book_infos where book_name like '{a}' or book_author like '{b}'".format(a=key,b=key)
        self.cursor.execute(sql)
        data = []
        for temp in self.cursor.fetchall():
            # print("get_book_infos_by_book_id = ", temp)
            data.append(temp)
        return data

    # 全部小说 分页查询数据   pagenum当前页数  pagesize每页条数
    def get_library_pagination(self, pagenum, pagesize):
        data = []
        # 总数据
        total_sql = "SELECT COUNT(*) as total FROM book_infos;"
        self.cursor.execute(total_sql)
        total = self.cursor.fetchone()

        # 分页查询
        page_sql = "SELECT id,book_id,book_cate,book_name,book_author,book_status,book_newest_name,book_newest_url FROM book_infos LIMIT {b} OFFSET {c};".format(b=pagesize, c=(pagenum-1) * pagesize)
        self.cursor.execute(page_sql)

        json_data = {
            "data": data,
            "total": total['total'],
            "status": 200,
            "msg": "请求成功"
        }

        for temp in self.cursor.fetchall():
            # print("get_book_infos_by_book_id = ", temp)
            data.append(temp)

        return json_data

    # 查询该书是否存在于用户的书架中
    def get_user_book(self, user_id, book_id):
        sql = 'select id from book_selfs where user_id = "{a}" and book_id = "{b}"'.format(a=user_id, b=book_id)
        self.cursor.execute(sql)  # 执行sql命令

        return self.cursor.fetchone()

    # 加入书架
    def Add_book_self(self,user_id,book_id):
        into_sql = 'insert into book_selfs(user_id,book_id) values ("%s","%s");' % (user_id, book_id)
        try:
            self.cursor.execute(into_sql)  # 执行sql命令
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            # 加入书架失败
            current_app.logger.error(e)
            return 412
        else:
            return 200

    # 我的书架分页查询数据   pagenum当前页数  pagesize每页条数
    def get_bookself_pagination(self, user_id, pagenum, pagesize):
        data = []
        # 查询用户书架总数
        total_sql = "SELECT COUNT(*) as total FROM book_selfs WHERE user_id = {};".format(user_id)
        self.cursor.execute(total_sql)
        total = self.cursor.fetchone()

        # 分页查询
        page_sql = "SELECT id,book_id,book_cate,book_name,book_author,DATE_FORMAT(book_last_update_time, '%Y-%m-%d %H:%i:%S') AS dtime,book_status,book_newest_name,book_newest_url FROM book_infos WHERE book_id IN (SELECT book_id FROM book_selfs WHERE user_id = '{a}') LIMIT {b} OFFSET {c};".format(a=user_id, b=pagesize, c=(pagenum-1) * pagesize)
        self.cursor.execute(page_sql)

        json_data = {
            "data": data,
            "uid": user_id,
            "total": total['total'],
            "status": 200,
            "msg": "请求成功"
        }

        for temp in self.cursor.fetchall():
            # print("get_book_infos_by_book_id = ", temp)
            data.append(temp)

        return json_data

    # 移除用户书架中的书籍信息
    def delete_book_infos(self, user_id, book_id):
        delete_sql = 'delete from book_selfs where user_id = "{a}" and book_id = "{b}";'.format(a=user_id, b=book_id)
        try:
            self.cursor.execute(delete_sql)  # 执行sql命令
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            # 删除失败
            current_app.logger.error(e)
            return 412
        else:
            return 200
